import requests
from datetime import datetime
from time import mktime
import hashlib
import json
import os
import pyzipper
import pymysql


# 企查查风险新闻
def qichachastart():
    try:
        for root, dirs, files in os.walk("./file", topdown=False):
            for name in files:
                os.remove(os.path.join(root, name))
            for name in dirs:
                os.rmdir(os.path.join(root, name))
        i1 = fx(datetime.now().date())
        i2 = news(datetime.now().date())
        log = "更新" + str(i1 + i2) + "条入库touzi_corp_fxjk/touzi_person_fxjk"
        savesql('INSERT INTO behc_log (name, log, update_time) VALUES ("企查查风险新闻", "' + log + '", "' + datetime.strftime(
            datetime.now(), '%Y-%m-%d %H:%M:%S') + '")')
    except Exception as e:
        print(e)


# 风险接口
def fx(orderDate):
    # 获取订单号
    orderlist = send_request('https://pro.qcc.com/api/radar/listLatestOrderNo',
                             orderDate)['resultList']
    # 遍历
    i = 0
    for order in orderlist:
        orderNo = order['orderNo']
        print(order)
        had = getsql('风险信息')
        # 是否已存储
        if orderNo not in had:
            # 获取下载地址、解压密码
            dl = send_request2('https://pro.qcc.com/api/radar/getDownloadUrl',
                               orderNo)
            url = dl['result']['downloadUrl']
            unzipPassword = dl['result']['unzipPassword']
            # 下载并解压文件，获取内容
            file, result = getfile(orderNo, url, unzipPassword,
                                   order['createDate'])
            print(file, result['totalSize'])
            if file == 'corp.json':
                table = 'touzi_corp_fxjk'
            else:
                table = 'touzi_person_fxjk'
            # 生成sql并入库
            for d in result['result']:
                sql = makesql(table, d, result['pushDate'])
                if savesql(sql) == '执行成功':
                    result['totalSize'] -= 1
            # 入库完保存当前订单号记录
            if result['totalSize'] == 0:
                savesql(
                    'INSERT INTO touzi_fxjk_orderno (orderNo, createDate, type) VALUES ("'
                    + orderNo + '", "' + order['createDate'] + '", "风险信息");')
            i += 1
            print(orderNo + '保存完成')
        else:
            print(orderNo + '已存在')
    return i


# 新闻接口
def news(orderDate):
    # 获取订单号
    orderlist = send_request('https://pro.qcc.com/api/radar/listNewsT0OrderNo',
                             orderDate)['resultList']
    # 遍历
    i = 0
    for order in orderlist:
        orderNo = order['orderNo']
        print(order)
        had = getsql('新闻信息')
        # 是否已存储
        if orderNo not in had:
            # 获取下载地址、解压密码
            dl = send_request2(
                'https://pro.qcc.com/api/radar/getNewsT0DownloadUrl', orderNo)
            url = dl['result']['downloadUrl']
            unzipPassword = dl['result']['unzipPassword']
            # 下载并解压文件，获取内容
            file, result = getfile(orderNo, url, unzipPassword,
                                   order['createDate'])
            print(file, result['totalSize'])
            # 生成sql并入库
            for d in result['result']:
                sql = makesql('touzi_corpnews_fxjk', d, result['pushDate'])
                if savesql(sql) == '执行成功':
                    result['totalSize'] -= 1
            # 入库完保存当前订单号记录
            if result['totalSize'] == 0:
                savesql(
                    'INSERT INTO touzi_fxjk_orderno (orderNo, createDate, type) VALUES ("'
                    + orderNo + '", "' + order['createDate'] + '", "新闻信息");')
            i += 1
            print(orderNo + '保存完成')
        else:
            print(orderNo + '已存在')
    return i


# 获取订单号
def send_request(url, orderDate):
    key = '633b8c152efd11eb990b0c42a106ce72'
    timespan = str(int(mktime(datetime.now().timetuple())))
    secretKey = '20KKVCV08SZA7XOFQ5BSS8JQUNFX3FLC'
    token = key + timespan + secretKey
    token = hashlib.md5(token.encode('utf-8')).hexdigest().upper()
    try:
        response = requests.get(
            url=url,
            params={
                "key": key,
                "orderDate": orderDate,
            },
            headers={
                "Token": token,
                "Timespan": timespan,
            },
        )
        print('Response HTTP Status Code: {status_code}'.format(
            status_code=response.status_code))
    except requests.exceptions.RequestException:
        print('HTTP Request failed')
    return json.loads(response.content)


# 获取下载地址、解压密码
def send_request2(url, order):
    key = '633b8c152efd11eb990b0c42a106ce72'
    timespan = str(int(mktime(datetime.now().timetuple())))
    secretKey = '20KKVCV08SZA7XOFQ5BSS8JQUNFX3FLC'
    token = key + timespan + secretKey
    token = hashlib.md5(token.encode('utf-8')).hexdigest().upper()
    try:
        response = requests.get(
            url=url,
            params={
                "key": key,
                "orderNo": order,
            },
            headers={
                "Token": token,
                "Timespan": timespan,
            },
        )
        print('Response HTTP Status Code: {status_code}'.format(
            status_code=response.status_code))
    except requests.exceptions.RequestException:
        print('HTTP Request failed')
    return json.loads(response.content)


# 下载并解压文件，获取内容
def getfile(orderNo, url, password, createDate):
    file = requests.get(url)
    createDate = createDate[:10]
    year, month, day = createDate.split('-')
    print(year, month, day)
    save_path = os.path.join("./file", year, month, day, orderNo + ".zip")
    save_path2 = os.path.join("./file", year, month, day, orderNo)
    if not os.path.exists(save_path2):
        os.makedirs(save_path2)
    with open(save_path, "wb") as code:
        code.write(file.content)
    with pyzipper.AESZipFile(save_path) as zf:
        zf.pwd = password.encode("utf-8")
        zf.extractall(path=save_path2)
    file = os.listdir(save_path2)[0]
    with open(os.path.join(save_path2, file), 'r') as read_stream:
        content = read_stream.read()
    return file, json.loads(content)


# 获取已存储订单号
def getsql(datatype):
    sql = 'SELECT orderNo FROM touzi_fxjk_orderno where type = "' + datatype + '";'
    con = None
    cur = None
    try:
        con = pymysql.connect(host="192.168.155.141",
                              port=3306,
                              user="root",
                              passwd="fD@LCVby2WJI",
                              database="behc_python",
                              charset='utf8')
        if con:
            print("链接成功")
            # 获取操作数据库的游标
            cur = con.cursor()
            # 执行sql语句
            cur.execute(sql)
            datas = cur.fetchall()
            datas2 = []
            for d in datas:
                datas2.append(d[0])
        else:
            print("链接失败")
    except Exception as e:
        print(e)
        datas2 = []
    finally:
        if con:
            if cur:
                cur.close()
            con.close()
    return datas2


# 生成sql
def makesql(table, d, pushDate):
    sql1 = ''
    sql2 = ''
    for k in list(d.keys()):
        sql1 += k + ','
        sql2 += '"' + str(d[k]) + '",'
    sql1 += 'pushDate'
    sql2 += '"' + pushDate + '"'
    return 'INSERT INTO ' + table + ' (' + sql1 + ') VALUES (' + sql2 + ');'


# 执行sql
def savesql(sql):
    con = None
    cur = None
    result = '执行成功'
    try:
        con = pymysql.connect(host="192.168.155.141",
                              port=3306,
                              user="root",
                              passwd="fD@LCVby2WJI",
                              database="behc_python",
                              charset='utf8')
        if con:
            print("链接成功")
            # 获取操作数据库的游标
            cur = con.cursor()
            # 执行sql语句
            cur.execute(sql)
            con.commit()
        else:
            print("链接失败")
    except Exception as e:
        result = e
    finally:
        if con:
            if cur:
                cur.close()
            con.close()
    print(result)
    return result


qichachastart()
